﻿/*---------------------------------------------------------------------------------
-----------------***DaLianQianYi***-----------------------
-----------------作者：大连仟亿科技------------------------------------------------------
-----------------联系作者：www.qianyikeji.com----------------------------------
-----------------时间：2018/5/24 9:02:38------------------------------------------
----------------------------------------------------------------------------------*/


using Donal.Xms.Common;
using Donal.Xms.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;

namespace Donal.Xms.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public partial class user_cart
    {
        private string databaseprefix; //数据库表名前缀
        public user_cart(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }

        #region 基本方法================================
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from  " + databaseprefix + "user_cart");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.user_cart model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
                                                     //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "user_cart(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), paras.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SqlConnection conn, SqlTransaction trans, Model.user_cart model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
                                                     //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "user_cart(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(SqlConnection conn, SqlTransaction trans, Model.user_cart model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("update  " + databaseprefix + "user_cart set ");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" where id=@id ");
            paras.Add(new SqlParameter("@id", model.id));
            return DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray()) > 0;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.user_cart model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("update  " + databaseprefix + "user_cart set ");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" where id=@id ");
            paras.Add(new SqlParameter("@id", model.id));
            return DbHelperSQL.ExecuteSql(strSql.ToString(), paras.ToArray()) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  " + databaseprefix + "user_cart ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.user_cart GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.user_cart model = new Model.user_cart();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "user_cart");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public int GetCount(int user_id)
        {
            //删除购物车中已不存在的商品
            DbHelperSQL.ExecuteSql("delete from dt_user_cart where goods_id not in(select id from dt_goods);");

            object obj = DbHelperSQL.GetSingle("select count(1) from " + databaseprefix + "user_cart where issuper =0 and is_lock=0 and user_id=" + user_id);

            if (obj != null)
            {
                return int.Parse(obj.ToString());
            }

            return 0;
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM " + databaseprefix + "user_cart");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetCartItemsList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" from (");
            strSql.Append(" select v.goods_id,v.goods_title,v.goods_unit,v.goods_size,v.goods_specs_values,v.goods_weight,v.packed_weight,v.img_url,v.arrive_quantity,");
            strSql.Append(" v.arriveDate,v.article_specs_values,v.color_name,v.for_sale,v.astrict_group_name,v.category_id,v.is_tehui,v.tehui_price,");
            strSql.Append(" v.integral,v.order_quantity,v.prewarning_value,v.sell_price,v.tags_name,v.trade_price,v.inventory,v.isDeleted,v.sumtransit transit_quantity,");
            strSql.Append(" v.styles,v.goods_bulk,v.goods_no,v.color_no,v.transit_data_id,v.product_tags,v.class_list ");
            strSql.Append(" from view_goods_cart v  where v.goods_id in(select goods_id from dt_user_cart c where " + strWhere + " )");

            strSql.Append(" ) t1   inner join");
            strSql.Append(" (select (select top 1 companyName from dt_company e where e.id = c.id) companyName,c.id,c.goods_id,c.add_time,c.quantity,c.user_id,c.cart_type,c.order_type,c.is_lock,c.issuper ");
            strSql.Append(" from dt_user_cart c where " + strWhere + " ");
            strSql.Append(" ) t2  on t1.goods_id = t2.goods_id");

            strSql.Append(" order by " + filedOrder);

            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "user_cart");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion

        #region 扩展方法================================
        /// <summary>
        /// 返回指定列
        /// </summary>
        public string GetValue(int id, string fileName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 " + fileName + " from " + databaseprefix + "user_cart");
            strSql.Append(" where id=" + id);
            string title = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(title))
            {
                return "";
            }
            return title;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(int goods_id, int user_id, int cart_type, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "user_cart set " + strValue);
            strSql.Append(" where user_id=" + user_id + " and goods_id =" + goods_id + " and cart_type =" + cart_type);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 将购物车中的商品设置占用标识islock=1
        /// </summary>
        public bool LockCartGoods(SqlConnection conn, SqlTransaction trans, string goods_ids, int user_id, int cart_type, int issuper)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "user_cart set is_lock=1");
            //strSql.Append(" where is_lock =0 and user_id=" + user_id + " and goods_id in(" + goods_ids + ") and issuper=" + issuper + " and cart_type =" + cart_type);
            strSql.Append(" where user_id=" + user_id + " and goods_id in(" + goods_ids + ") and issuper=" + issuper + " and cart_type =" + cart_type);
            return DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString()) > 0;
        }

        /// <summary>
        /// 将购物车中的商品的占用标识解除
        /// </summary>
        public bool UnLockCartGoods(SqlConnection conn, SqlTransaction trans, int goods_id, int user_id, int cart_type, int issuper)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "user_cart set is_lock=0");
            strSql.Append(" where is_lock =1 and user_id=" + user_id + " and goods_id =" + goods_id + " and issuper=" + issuper + " and cart_type =" + cart_type);
            return DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString()) > 0;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "user_cart set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.user_cart DataRowToModel(DataRow row)
        {
            Model.user_cart model = new Model.user_cart();
            if (row != null)
            {
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        public bool Delete(string where)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  " + databaseprefix + "user_cart ");
            strSql.Append(" where " + where);

            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }
        #endregion
    }
}

